﻿/* ----------------------------------------------------------------------------   
 * Copyright:
 * Wuxi Huaxia Computer Technology Co.,Ltd. All rights reserved. 
 *
 * Module Description:
 * XmlProcedure 解析xml文档
 *  
 * Namespace:
 *  Wxhx.EntLib.Data
 *
 *  * Class Description:
 *
 *
 * Comment 					        Revision	    Date	  Coder
 * -----------------------------    --------    --------  --------------
 * Created							1.0		    2006-5-1   huyb@wxhx.com
 * Updated							1.0		
 *
 *
 * ------------------------------------------------------------------------------*/

using System;
using System.IO;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Data;
using System.Configuration;
using System.Web;

namespace SaleStat.COM.SQLHelper
{
    public class XmlProcedure
    {
        /// <summary>
        /// XML PATH
        /// </summary>
        private static string xmlpath = HttpContext.Current.Request.MapPath(ConfigurationManager.AppSettings["sqlPath"].ToString());
        //private static string xmlpath = @"E:\ghrc\NET\WORKREPORT\WKRPT.Entity\XML\";

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlId"></param>
        /// <returns></returns>
        public static string BuildSqlString(string pageId, string sqlId)
        {

            XmlDocument xDoc = new XmlDocument();

            string path = "";
            // 
            string sqlnode = "";
            // 
            string sqlString = "";

            path = xmlpath + pageId + ".xml";
            sqlnode = "/DataFactory/" + sqlId;

            try
            {

                if (!File.Exists(path))
                {

                    return null;
                }



                xDoc.Load(path);

                XmlNode child = xDoc.SelectSingleNode(sqlnode);

                if (child != null)
                {
                    XmlNodeReader nr = new XmlNodeReader(child);
                    while (nr.Read())
                    {
                        if (nr.NodeType == XmlNodeType.CDATA)
                        {
                            sqlString = nr.Value;
                        }
                    }
                }

            }
            catch { }

            return sqlString;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlId"></param>
        /// <param name="parmsList"></param>
        /// <param name="xmlParmsList"></param>
        /// <returns></returns>
        public static bool BuildSqlParms(string pageId, string sqlId, ArrayList parmsList, ref ArrayList xmlParmsList)
        {

            XmlDocument xDoc = new XmlDocument();

            string path = "";
            string parmnode = "";

            path = xmlpath + pageId + ".xml";
            parmnode = "/DataFactory/" + sqlId + "/Parameters";

            try
            {

                xDoc.Load(path);

                XmlNode child = xDoc.SelectSingleNode(parmnode);

                if (child != null)
                {
                    XmlNodeReader reader = new XmlNodeReader(child);
                    int index = 0;

                    while (reader.Read())
                    {
                        reader.MoveToContent();
                        if (reader.HasAttributes)
                        {
                            string sqlName1 = "";
                            DbType dbType = new DbType();
                            int length = 0;
                            string direction;

                            for (int i = 0; i < reader.AttributeCount; i++)
                            {
                                reader.MoveToAttribute(i);
                                switch (reader.Name)
                                {
                                    case "SqlName":
                                        sqlName1 = reader.Value;
                                        break;

                                    case "type":
                                        dbType = ConvertDbType(Convert.ToString(reader.Value));
                                        break;

                                    case "length":
                                        length = Convert.ToInt32(reader.Value);
                                        break;
                                    case "direction":
                                        direction = reader.Value;
                                        break;
                                }


                            }

                            xmlParmsList.Add(new XmlParameter(sqlName1, dbType, parmsList[index]));




                            //Return the reader to the book element.
                            reader.MoveToElement();
                            index = index + 1;
                        }
                    }
                }
                else
                {
                    // 
                    return false;
                }
            }
            catch
            {

            }


            return true;


        }

        /// <summary>
        /// 组合综合查询的SQL 文
        /// </summary>
        /// <param name="select"></param>
        /// <param name="pageId"></param>
        /// <param name="sqlId"></param>
        /// <returns></returns>
        public static string BuildCombineString(string pageId, string sqlId, ArrayList parmsList)
        {
            XmlDocument xDoc = new XmlDocument();

            string path = "";
            string parmnode = "";
            string sqlText = "";

            path = xmlpath + pageId + ".xml";

            try
            {
                xDoc.Load(path);

                sqlText = BuildSqlString(pageId, sqlId);

                if (sqlText.Length == 0)
                {
                    return null;
                }

                parmnode = "/DataFactory/" + sqlId + "/Parameters";
                XmlNode child = xDoc.SelectSingleNode(parmnode);

                if (child == null)
                {
                    return null;
                }

                XmlNodeReader reader = new XmlNodeReader(child);
                int index = 0;

                while (reader.Read())
                {
                    reader.MoveToContent();
                    if (reader.HasAttributes)
                    {
                        string sqlName1 = "";
                        DbType dbType = new DbType();
                        int length = 0;
                        string direction;
                        string param = "";


                        for (int i = 0; i < reader.AttributeCount; i++)
                        {
                            reader.MoveToAttribute(i);
                            switch (reader.Name)
                            {
                                case "SqlName":
                                    sqlName1 = reader.Value;
                                    break;

                                case "type":
                                    dbType = ConvertDbType(Convert.ToString(reader.Value));
                                    break;

                                case "length":
                                    length = Convert.ToInt32(reader.Value);
                                    break;
                                case "direction":
                                    direction = reader.Value;
                                    break;

                            }


                        }

                        switch (dbType)
                        {
                            case DbType.String:
                                if (parmsList[index] == null || parmsList[index].ToString().Trim() == "")
                                {
                                    param = "null or 1=1";
                                }
                                else
                                {
                                    if (parmsList[index].ToString().Trim().Equals("''"))
                                    {
                                        param = Convert.ToString(parmsList[index]).Trim();
                                    }
                                    else
                                    {
                                        param = Convert.ToString(parmsList[index]).Trim();
                                        param = "'" + param + "'";
                                    }
                                }

                                break;

                            case DbType.Date:
                                if (parmsList[index] == null || parmsList[index].ToString().Trim() == "" || Convert.ToDateTime(parmsList[index]).ToShortDateString() == "0001-1-1")
                                {
                                    param = "null";
                                }
                                else
                                {
                                    param = Convert.ToDateTime(parmsList[index]).ToString();
                                    param = "'" + param + "'";
                                }

                                break;


                            case DbType.DateTime:
                                if (parmsList[index] == null || parmsList[index].ToString().Trim() == "" || Convert.ToDateTime(parmsList[index]).ToShortDateString() == "0001-1-1")
                                {
                                    param = "null";
                                }
                                else
                                {
                                    param = Convert.ToDateTime(parmsList[index]).ToString();
                                    param = "'" + param + "'";
                                }

                                break;
                            case DbType.Boolean:
                                if (parmsList[index] == null)
                                {
                                    param = "null or 1=1";
                                }
                                else
                                {
                                    if ((bool)(parmsList[index]))
                                        param = "1";
                                    else
                                        param = "0";
                                }
                                break;

                            default:// 默认为,数值类型
                                if (parmsList[index] == null)
                                {
                                    param = "null or 1=1";
                                }
                                else
                                {
                                    param = Convert.ToString(parmsList[index]);
                                }

                                break;
                        }

                        // Replace;
                        sqlText = sqlText.Replace(sqlName1, param);

                        //Return the reader to the book element.
                        reader.MoveToElement();
                        index = index + 1;
                    }
                }

            }
            catch
            {
                throw;
            }

            return sqlText;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbTypeString"></param>
        /// <returns></returns>
        private static DbType ConvertDbType(string dbTypeString)
        {
            DbType dbType = DbType.String;

            switch (dbTypeString.ToLower())
            {
                case "bigint":
                    dbType = DbType.Int64;
                    break;
                case "binary":
                    dbType = DbType.Binary;
                    break;
                case "bit":
                    dbType = DbType.Boolean;
                    break;
                case "char":
                    dbType = DbType.String;
                    break;
                case "datetime":
                    dbType = DbType.DateTime;
                    break;
                case "decimal":
                    dbType = DbType.Decimal;
                    break;
                case "float":
                    dbType = DbType.Double;
                    break;
                case "int":
                    dbType = DbType.Int32;
                    break;
                case "money":
                    dbType = DbType.Decimal;
                    break;
                case "nchar":
                    dbType = DbType.String;
                    break;
                case "ntext":
                    dbType = DbType.String;
                    break;
                case "numeric":
                    dbType = DbType.Decimal;
                    break;
                case "nvarchar":
                    dbType = DbType.String;
                    break;
                case "real":
                    dbType = DbType.Single;
                    break;
                case "smalldatetime":
                    dbType = DbType.DateTime;
                    break;
                case "smallint":
                    dbType = DbType.Int16;
                    break;
                case "smallmoney":
                    dbType = DbType.Decimal;
                    break;
                case "text":
                    dbType = DbType.String;
                    break;
                case "timestamp":
                    dbType = DbType.Binary;
                    break;
                case "tinyint":
                    dbType = DbType.Byte;
                    break;
                case "varchar":
                    dbType = DbType.String;
                    break;
                case "image":
                    dbType = DbType.Binary;
                    break;
                default:
                    dbType = DbType.String;
                    break;
            }

            return dbType;
        }
    }
}
